#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
报货销售查询
"""
import pymysql
import pandas as pd
import sqlite3
from config import DB_CONFIG, STORE_CODE, QUERY_PERIOD

df_single_cup_consumption = pd.read_excel('调用底表.xlsx', sheet_name='报货映射',usecols=['存货编码','主料名称','最小单位量','最小单位名称'],dtype={'存货编码':str})
# 读取调用原物料sku编码
unique_sku_codes = df_single_cup_consumption['存货编码'].unique()
sku_codes_str = ','.join([f"'{str(code).zfill(9)}'" for code in unique_sku_codes])
# 读取“单杯消耗”底表
df_report_mapping = pd.read_excel('调用底表.xlsx', sheet_name='单杯消耗',usecols=['产品名称','主料名称','单杯用量','单位'])
# 读取“单杯消耗”底表，获取产品名称
bom_products = df_report_mapping['产品名称'].unique()
bom_products_str = ", ".join([f"'{product}'" for product in bom_products])
# 读取配置的门店编号
store_code_str = ','.join([f"'{code}'" for code in STORE_CODE])

report_sql = f"""
WITH report_order AS ( 
    SELECT 
        t2.id AS 订单ID, 
        t2.store_code AS 门店编号, 
        t2.order_time AS 订单时间
    FROM dwd_rps_tll_order_di t2      
), 

-- 选择唯一订单ID 
unique_orders AS ( 
    SELECT 
        订单ID, 
        门店编号, 
        订单时间,
        ROW_NUMBER() OVER(PARTITION BY 订单ID ORDER BY 订单时间 DESC) AS rn 
    FROM report_order 
), 


report_order_details AS ( 
    SELECT 
        order_id AS 订单ID, 
        product_info AS 存货名称, 
        sku_code AS 存货编码, 
        quantity AS 数量 
    FROM 
        dwd_rps_tll_order_details_di 
), 

summary_table AS ( 
    SELECT 
        uo.门店编号, 
        rod.存货名称, 
        rod.存货编码, 
        rod.数量,
        uo.订单时间
    FROM 
        unique_orders uo 
    LEFT JOIN 
        report_order_details rod 
    ON 
        uo.订单ID = rod.订单ID 
    WHERE 
        rod.存货编码 IN ({sku_codes_str})
        AND uo.rn = 1
        AND uo.门店编号 IN ({store_code_str})
) 
SELECT 
    门店编号,
    存货名称,
    存货编码,
    SUM(数量) AS 数量
FROM summary_table 
WHERE date(订单时间) BETWEEN '{QUERY_PERIOD['start_date']} 00:00:00' AND '{QUERY_PERIOD['end_date']} 00:00:00'
GROUP BY 门店编号, 存货名称, 存货编码
ORDER BY 门店编号, 存货名称
"""

sales_sql = f"""
    SELECT stat_shop_id as 门店编号, item_name as 产品名称, sum(dp_item_count) as 销量
    FROM ads_dbs_trade_food_di
    WHERE business_date BETWEEN {QUERY_PERIOD['start_date'].replace('-', '')} and {QUERY_PERIOD['end_date'].replace('-', '')} 
    and stat_shop_id in ({store_code_str})
    and item_name IN ({bom_products_str})
    GROUP BY stat_shop_id, item_name
"""
# 执行查询
connection = pymysql.connect(**DB_CONFIG)
print('查询报货数据')
df_report = pd.read_sql(report_sql, connection)
print('查询销售数据')
df_sales = pd.read_sql(sales_sql, connection)
# 关闭数据库连接
connection.close()
# 处理报货数据
df_report_merged = pd.merge(df_single_cup_consumption, df_report, on='存货编码', how='left')
df_report_merged['报货总量'] = df_report_merged['数量'] * df_report_merged['最小单位量']
df_report_merged = df_report_merged.loc[:,['门店编号', '存货名称', '存货编码', '数量', '主料名称', '最小单位名称','最小单位量', '报货总量']]

# 处理销售数据
df_sales_merged = pd.merge(df_sales, df_report_mapping, on='产品名称', how='left')
df_sales_merged['主料消耗量'] = df_sales_merged['销量'] * df_sales_merged['单杯用量']
df_sales_merged = df_sales_merged[['门店编号', '产品名称', '销量', '主料名称', '单杯用量', '主料消耗量', '单位']]

# 保存结果到SQLite数据库
sqlite_conn = sqlite3.connect('temp.db')
df_sales_merged.to_sql('sales_query', sqlite_conn, if_exists='replace', index=False)
df_report_merged.to_sql('report_query', sqlite_conn, if_exists='replace', index=False)
sqlite_conn.close()
print("已保存至SQLite数据库")